import pymysql
from common.Config import settings


class DBManager:
	def __init__(self):
		self.conn = self.connect()
		self.cursor = self.conn.cursor()
		self.records = []

	def connect(self):
		try: 
			conn = pymysql.connect(
				host= settings.DB_HOST,
				port= settings.DB_PORT,
				user= settings.DB_USER,
				password= settings.DB_PASSWORD,
				database= settings.DB_NAME
			)
		except pymysql.MySQLError as e:
			print()
			print("连接数据库失败:", e)
			return None
		return conn

	def insert_data(self, table: str, fields: list, data: list):

		if len(data) <= 0 :
			return

		db = self.conn
		cursor = self.cursor

		field = ','.join(fields)
		placeholder = ', '.join(['%s'] * len(fields))
		sql = f'''INSERT INTO {table} ({field}) VALUES ({placeholder})'''

		ids = []
		try:
			for row in data:
				cursor.execute(sql, row)
				# print(sql,row)
				ids.append(row[0])  # 记录每次插入的自增id
		except pymysql.MySQLError as e:
			print()
			print("插入失败:", e)
			db.rollback()
			return []
		else:
			db.commit()
		self.records.append({
			"table":table,
			"field":fields[0],
			"ids":ids
		})

	def delete_data(self, table:str, field:str, data:list):
		db = self.conn
		cursor = self.cursor

		# print(table)
		# print(field)
		# print(data)

		placeholder = '%s'
		sql = f"DELETE FROM {table} WHERE {field}={placeholder}"

		try:
			cursor.executemany(sql,data)
		except pymysql.MySQLError as e:
			print("删除失败:",e)
			db.rollback()
		else:
			db.commit()

	def clear_data(self):
		print("测试")
		for record in self.records:
			self.delete_data(record['table'], record['field'], record['ids'])



	def close(self):
		self.clear_data()
		self.cursor.close()
		self.conn.close()

